package com.iris.live.services.report.sources;

import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.ReportConfig;
import com.iris.live.services.report.data.BaseReportDataSource;
import com.iris.live.services.report.data.ReportContext;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 金融漏斗报表数据源
 * @author: wangzhenyan
 * date: 2017/8/29 14:24
 */
@Component("FinanceFunnelDataSource")
public class FinanceFunnelDataSource extends BaseReportDataSource{
    @Override
    public List getData(ReportContext context, List<ReportConfig> config) throws Exception {
        Map info= context.getParams() ;
        String lineType=info.get("displayType").toString();
        List<Map> datas =new ArrayList<>();
        if("1".equals(lineType)){ // 时间维度
            datas = getTimeLineData(info);
        } else if ("2".equals(lineType)) {
            datas = getOrgLineData(info); // 地域维度
        }
        else if ("6".equals(lineType)){ // 车型维度
            datas = getBrandLineData(info);
        } else {
            datas = getFinanceOrgLineData(info); // 金融机构维度
        }

        super.brandGroup(context ,datas);
        return datas;
    }

    private List getTimeLineData(Map info) {
        Map params = new HashMap();
        StringBuffer sql = new StringBuffer(
                "select  " +
                " g.date, " +
                " sum(g.first_inroom_clue_num) first_inroom_clue_num, " +
                " sum(g.order_num) order_num, " +
                " sum(g.retail_num) retail_num, " +
                " sum(g.fin_quotation_num) fin_quotation_num, " +
                " sum(g.fin_application_num) fin_application_num, " +
                " sum(g.fin_application_retail_num) fin_application_retail_num, " +
                " sum(g.fin_passed_retail_num) fin_passed_retail_num " +
                "from ( " +
                " select d.* from ( " +
                "  select a.permit,right(a.sale_month,2) as date,a.sale_month,a.vehicle_model_id,a.first_inroom_clue_num,a.order_num,a.retail_num,0 fin_quotation_num,0 fin_application_num,0 fin_application_retail_num,0 fin_passed_retail_num " +
                "  from t_rpt_data_sale_info a " +
                "  union  " +
                "  select b.permit,right(b.sale_month,2) as date,b.sale_month,b.vehicle_model_id,0 first_inroom_clue_num,0 order_num,0 retail_num,b.fin_quotation_num,b.fin_application_num,b.fin_application_retail_num,b.fin_passed_retail_num  " +
                "  from t_rpt_data_finance_sale_info b " +
                "  join lsh金融机构设置 c on b.fin_org_id = c.金融机构编号 " +
                "  where 1=1 ");

        String selFinType = info.get("selFinType") == null ? "" : info.get("selFinType").toString();
        if (!StringUtils.isNullOrBlank(selFinType) && !"all".equals(selFinType) && !"全部".equals(selFinType)) { // 金融贷款类型
            sql.append(" and b.fin_loan_type = :selFinType ");
            params.put("selFinType",selFinType);
        }

        String selFinIns = info.get("selFinIns") == null ? "" : info.get("selFinIns").toString();
        if(!StringUtils.isNullOrBlank(selFinIns) && !"all".equals(selFinIns) && !"全部".equals(selFinIns)) { // 金融机构
            sql.append(" and c.金融机构 = :selFinIns ");
            params.put("selFinIns",selFinIns);
        }

        sql.append(
                " ) d " +
                " join lsh经销商表 e on d.permit = e.许可_BMBS " +
                " join lsh车型 f on d.vehicle_model_id = f.车型编号  " +
                " where 1=1 ");

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            sql.append("and d.sale_month >= :startMonth and d.sale_month <= :endMonth ");
            params.put("startMonth",startMonth);
            params.put("endMonth",endMonth);
        }

        String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql.append("and (e.大区 = :permit or e.`大区_EN` = :permit or e.小区 = :permit or e.`小区_EN` = :permit or e.CTA = :permit or e.CTA_EN = :permit or e.`许可_BMBS` = :permit) ");
            params.put("permit", permit);
        }

        String carType = info.get("carType") == null ? "" : info.get("carType").toString();
        if(!StringUtils.isNullOrBlank(carType) && !"all".equals(carType) && !"全部".equals(carType)) {
            sql.append("and (f.品牌 = :carType or f.级别 = :carType or f.车型 = :carType) ");
            params.put("carType",carType);
        }

        sql.append(") g group by g.date");
        return this.getList(sql.toString(),params);
    }

    private List getOrgLineData(Map info) {
        Map params = new HashMap();
        StringBuffer sql = new StringBuffer(
                        "select  " +
                        " 'LSH' 集团,g.许可_LSH,g.大区 AS 大区,g.大区_EN AS 大区_EN,g.小区 AS 小区,g.小区_EN AS 小区_EN,  " +
                        " IF ( isnull(g.CTA) OR g.CTA = '', '其他cta',g.CTA ) AS CTA,   " +
                        " IF (isnull(g.CTA_EN) OR g.CTA_EN = '', 'Other CTA',g.CTA_EN ) AS CTA_EN,  " +
                        " g.许可_BMBS AS 许可_BMBS,  " +
                        " g.经销商名 AS 经销商名,  " +
                        " g.经销商名_EN AS 经销商名_EN,  " +
                        " g.SalesCode AS salesCode,  " +
                        " g.permit, " +
                        " sum(g.first_inroom_clue_num) first_inroom_clue_num, " +
                        " sum(g.order_num) order_num, " +
                        " sum(g.retail_num) retail_num, " +
                        " sum(g.fin_quotation_num) fin_quotation_num, " +
                        " sum(g.fin_application_num) fin_application_num, " +
                        " sum(g.fin_application_retail_num) fin_application_retail_num, " +
                        " sum(g.fin_passed_retail_num) fin_passed_retail_num " +
                        "from ( " +
                        " select e.*,d.* from ( " +
                        "  select a.permit,right(a.sale_month,2) as date,a.sale_month,a.vehicle_model_id,a.first_inroom_clue_num,a.order_num,a.retail_num,0 fin_quotation_num,0 fin_application_num,0 fin_application_retail_num,0 fin_passed_retail_num " +
                        "  from t_rpt_data_sale_info a " +
                        "  union  " +
                        "  select b.permit,right(b.sale_month,2) as date,b.sale_month,b.vehicle_model_id,0 first_inroom_clue_num,0 order_num,0 retail_num,b.fin_quotation_num,b.fin_application_num,b.fin_application_retail_num,b.fin_passed_retail_num  " +
                        "  from t_rpt_data_finance_sale_info b " +
                        "  join lsh金融机构设置 c on b.fin_org_id = c.金融机构编号 " +
                        " where 1=1 ");

        String selFinType = info.get("selFinType") == null ? "" : info.get("selFinType").toString();
        if (!StringUtils.isNullOrBlank(selFinType) && !"all".equals(selFinType) && !"全部".equals(selFinType)) { // 金融贷款类型
            sql.append(" and b.fin_loan_type = :selFinType ");
            params.put("selFinType",selFinType);
        }

        String selFinIns = info.get("selFinIns") == null ? "" : info.get("selFinIns").toString();
        if(!StringUtils.isNullOrBlank(selFinIns) && !"all".equals(selFinIns) && !"全部".equals(selFinIns)) { // 金融机构
            sql.append(" and c.金融机构 = :selFinIns ");
            params.put("selFinIns",selFinIns);
        }

        sql.append(
                " ) d " +
                " join lsh经销商表 e on d.permit = e.许可_BMBS " +
                " join lsh车型 f on d.vehicle_model_id = f.车型编号  " +
                " where 1=1 ");

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            sql.append("and d.sale_month >= :startMonth and d.sale_month <= :endMonth ");
            params.put("startMonth",startMonth);
            params.put("endMonth",endMonth);
        }

        String carType = info.get("carType") == null ? "" : info.get("carType").toString();
        if(!StringUtils.isNullOrBlank(carType) && !"all".equals(carType) && !"全部".equals(carType)) {
            sql.append("and (f.品牌 = :carType or f.级别 = :carType or f.车型 = :carType) ");
            params.put("carType",carType);
        }

        sql.append(") g group by g.permit");
        return this.getList(sql.toString(),params);
    }

    private List getBrandLineData(Map info) {
        Map params = new HashMap();
        StringBuffer sql = new StringBuffer(
                        "select  " +
                        " g.品牌 brand, " +   // 品牌别名需要为 brand
                        " g.级别 series, " +  // 车系别名需要为 series
                        " g.车型 model, " +  // 车型别名需要为 model
                        " sum(g.first_inroom_clue_num) first_inroom_clue_num, " +
                        " sum(g.order_num) order_num, " +
                        " sum(g.retail_num) retail_num, " +
                        " sum(g.fin_quotation_num) fin_quotation_num, " +
                        " sum(g.fin_application_num) fin_application_num, " +
                        " sum(g.fin_application_retail_num) fin_application_retail_num, " +
                        " sum(g.fin_passed_retail_num) fin_passed_retail_num " +
                        "from ( " +
                        " select d.*,f.品牌,f.级别,f.车型 from ( " +
                        "  select a.permit,right(a.sale_month,2) as date,a.sale_month,a.vehicle_model_id,a.first_inroom_clue_num,a.order_num,a.retail_num,0 fin_quotation_num,0 fin_application_num,0 fin_application_retail_num,0 fin_passed_retail_num " +
                        "  from t_rpt_data_sale_info a " +
                        "  union  " +
                        "  select b.permit,right(b.sale_month,2) as date,b.sale_month,b.vehicle_model_id,0 first_inroom_clue_num,0 order_num,0 retail_num,b.fin_quotation_num,b.fin_application_num,b.fin_application_retail_num,b.fin_passed_retail_num  " +
                        "  from t_rpt_data_finance_sale_info b " +
                        "  join lsh金融机构设置 c on b.fin_org_id = c.金融机构编号 " +
                        "  where 1=1 ");

        String selFinType = info.get("selFinType") == null ? "" : info.get("selFinType").toString();
        if (!StringUtils.isNullOrBlank(selFinType) && !"all".equals(selFinType) && !"全部".equals(selFinType)) { // 金融贷款类型
            sql.append(" and b.fin_loan_type = :selFinType ");
            params.put("selFinType",selFinType);
        }

        String selFinIns = info.get("selFinIns") == null ? "" : info.get("selFinIns").toString();
        if(!StringUtils.isNullOrBlank(selFinIns) && !"all".equals(selFinIns) && !"全部".equals(selFinIns)) { // 金融机构
            sql.append(" and c.金融机构 = :selFinIns ");
            params.put("selFinIns",selFinIns);
        }

        sql.append(
                " ) d " +
                " join lsh经销商表 e on d.permit = e.许可_BMBS " +
                " join lsh车型 f on d.vehicle_model_id = f.车型编号  " +
                " where 1=1 " +
                " and f.车型 is not null and f.车型 <> ''  ");

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            sql.append("and d.sale_month >= :startMonth and d.sale_month <= :endMonth ");
            params.put("startMonth",startMonth);
            params.put("endMonth",endMonth);
        }

        String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql.append("and (e.大区 = :permit or e.`大区_EN` = :permit or e.小区 = :permit or e.`小区_EN` = :permit or e.CTA = :permit or e.CTA_EN = :permit or e.`许可_BMBS` = :permit) ");
            params.put("permit", permit);
        }

        String carType = info.get("carType") == null ? "" : info.get("carType").toString();
        if(!StringUtils.isNullOrBlank(carType) && !"all".equals(carType) && !"全部".equals(carType)) {
            sql.append("and (f.品牌 = :carType or f.级别 = :carType or f.车型 = :carType) ");
            params.put("carType",carType);
        }

        sql.append(") g group by g.车型");
        return this.getList(sql.toString(),params);
    }

    private List getFinanceOrgLineData(Map info) {
        Map params = new HashMap();
        StringBuffer sql = new StringBuffer(
                        "select  " +
                        " if(g.金融机构 = '' or g.金融机构 is null,'其他',g.金融机构) 金融机构, " +
                        " sum(g.first_inroom_clue_num) first_inroom_clue_num, " +
                        " sum(g.order_num) order_num, " +
                        " sum(g.retail_num) retail_num, " +
                        " sum(g.fin_quotation_num) fin_quotation_num, " +
                        " sum(g.fin_application_num) fin_application_num, " +
                        " sum(g.fin_application_retail_num) fin_application_retail_num, " +
                        " sum(g.fin_passed_retail_num) fin_passed_retail_num " +
                        "from ( " +
                        " select d.*,f.品牌 from ( " +
                        "  select '其他' 金融机构,a.permit,right(a.sale_month,2) as date,a.sale_month,a.vehicle_model_id,a.first_inroom_clue_num,a.order_num,a.retail_num,0 fin_quotation_num,0 fin_application_num,0 fin_application_retail_num,0 fin_passed_retail_num " +
                        "  from t_rpt_data_sale_info a " +
                        "  union all " +
                        "  select c.金融机构,b.permit,right(b.sale_month,2) as date,b.sale_month,b.vehicle_model_id,0 first_inroom_clue_num,0 order_num,0 retail_num,b.fin_quotation_num,b.fin_application_num,b.fin_application_retail_num,b.fin_passed_retail_num  " +
                        "  from t_rpt_data_finance_sale_info b " +
                        "  join lsh金融机构设置 c on b.fin_org_id = c.金融机构编号 " +
                        "  where 1=1 ");

        sql.append(
                " ) d " +
                " join lsh经销商表 e on d.permit = e.许可_BMBS " +
                " join lsh车型 f on d.vehicle_model_id = f.车型编号  " +
                " where 1=1 ");

        String startMonth = info.get("startMonth") == null ? "" : info.get("startMonth").toString();
        String endMonth =  info.get("endMonth") == null ? "" : info.get("endMonth").toString();
        if (StringUtils.isNotNullOrBlank(startMonth) && StringUtils.isNotNullOrBlank(endMonth)) {
            sql.append("and d.sale_month >= :startMonth and d.sale_month <= :endMonth ");
            params.put("startMonth",startMonth);
            params.put("endMonth",endMonth);
        }

        String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql.append("and (e.大区 = :permit or e.`大区_EN` = :permit or e.小区 = :permit or e.`小区_EN` = :permit or e.CTA = :permit or e.CTA_EN = :permit or e.`许可_BMBS` = :permit) ");
            params.put("permit", permit);
        }

        String carType = info.get("carType") == null ? "" : info.get("carType").toString();
        if(!StringUtils.isNullOrBlank(carType) && !"all".equals(carType) && !"全部".equals(carType)) {
            sql.append("and (f.品牌 = :carType or f.级别 = :carType or f.车型 = :carType) ");
            params.put("carType",carType);
        }

        sql.append(") g group by g.金融机构");

        List<Map> datas = this.getList(sql.toString(),params);
        if(datas != null){
            for(Map data : datas){

            }
        }
        return datas;
        //this.getList(sql.toString(),params);
    }

}
